<?php
/**
 * DooSqlMagic class file.
 *
 * @author Leng Sheng Hong <darkredz@gmail.com>
 * @link http://www.doophp.com/
 * @copyright Copyright &copy; 2009 Leng Sheng Hong
 * @license http://www.doophp.com/license
 */


/**
 * DooSqlMagic is a fast ORM tool which handles relational data.
 *
 * <p>It generates SQL query on the fly and escaping/quoting record values to prevent sql injection.
 * You can do CRUD operations in an easier way with DooSqlMagic instead of writing queries manually.</p>
 *
 * <p>Database models does not need to extend any classes with DooSqlMagic. Database table relationships are defined in a
 * centralized way in <i>SITE_PATH/protected/config/db.conf.php</i> instead of defining them in each Model class.</p>
 *
 * <p>Unlike most active records, DooSqlMagic class does not use any php Magic methods in order to gain higher performance.</p>
 *
 * <p>It does not implements the active record design pattern, a popular Object-Relational Mapping (ORM) technique.
 * However, it can still be used with some flavours of active record features.</p>
 *
 * <p>DooSqlMagic is more like a blend between Active record and DataMapper design pattern.</p>
 *
 * <p>Creating an instance of an DooSqlMagic class does not immediately connect to the RDBMS server.
 *  Use connect() or reconnect() to make the actual connection on demand.</p>
 *
 * Please check {@link http://www.doophp.com/doc/guide/database the Guide} for more details
 * about this class.
 *
 * @author Leng Sheng Hong <darkredz@gmail.com>
 * @version $Id: DooSqlMagic.php 1000 2009-07-7 18:27:22
 * @package doo.db
 * @since 1.0
 */
class DooSqlMagic {

	/**
	 * Enable/disable SQL tracking, to view SQL which has been queried, use showSQL()
	 * @var bool
	 */
	public $sql_tracking=false;

	/**
	 * Determined whether the database connection is made.
	 * @var bool
	 */
	public $connected = false;

	/**
	 * Path to the folder where the model class files are located. Ends with a slash. eg. /var/www/modelfiles/
	 * @var string
	 */
	public $modelPath;

	protected $map;
	protected $dbconfig;
	protected $dbconfig_list;
	protected $pdo;

	protected $transactionLevel = 0;

	protected $sql_list;

	const JOIN_LEFT = 'LEFT';
	const JOIN_RIGHT = 'RIGHT';
	const JOIN_LEFT_OUTER = 'LEFT OUTER';
	const JOIN_RIGHT_OUTER = 'RIGHT OUTER';
	const JOIN_INNER = 'INNER';
	const JOIN = '';

	public function loadModel($className, $createObj=false){
		if( class_exists($className, false)===true ){
			if($createObj===true){
				return new $className;
			}
			return;
		}

		if(empty($this->modelPath)===true) {
			return Doo::loadModel($className, $createObj);
		}
		else {
			require_once $this->modelPath . $className . '.php';

			if($createObj===true){
				return new $className;
			}
		}
	}

	/**
	 * Set the database configuration
	 * @param array $dbconfig Associative array of the configurations
	 * @param string $default_name Default configuration to be used
	 */
	public function setDb($dbconfig, $default_name){
		$this->dbconfig_list = $dbconfig;
		$this->dbconfig = $dbconfig[$default_name];
	}

	/**
	 * Get the default database configuration
	 * @return array array(db_host, db_name, db_user, db_pwd, db_driver, db_connection_cache)
	 */
	public function getDefaultDbConfig(){
		return $this->dbconfig;
	}

	/**
	 * Set the database relationship mapping
	 * @param array $dbmap Database relationship mapping
	 */
	public function setMap($dbmap){
		$this->map = $dbmap;
	}

	/**
	 * Append to DB relationship mapping
	 * @param array $dbmap Database relationship mapping
	 */
	public function appendMap( $dbmap ){
		if(is_array($this->map)===True)
		$this->map = array_merge( $this->map, $dbmap );
		else
		$this->map = $dbmap;
	}

	/**
	 * Get DB relationship mapping
	 * @return array Database relationship mapping
	 */
	public function getMap(){
		return $this->map;
	}

	/**
	 * Connects to the database with the default database connection configuration
	 */
	public function connect(){
		if($this->dbconfig==NULL)return;

		try{
			if ($this->dbconfig[4]=='sqlite')
			$this->pdo = new PDO("{$this->dbconfig[4]}:{$this->dbconfig[0]}");
			else
			$this->pdo = new PDO("{$this->dbconfig[4]}:host={$this->dbconfig[0]};dbname={$this->dbconfig[1]}", $this->dbconfig[2], $this->dbconfig[3],array(PDO::ATTR_PERSISTENT => $this->dbconfig[5]));
			$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->connected = true;
			if(isset($this->dbconfig['charset']) && isset($this->dbconfig['collate'])){
				$this->pdo->exec("SET NAMES '". $this->dbconfig['charset']. "' COLLATE '". $this->dbconfig['collate'] ."'");
			}
			else if(isset($this->dbconfig['charset']) ){
				$this->pdo->exec("SET NAMES '". $this->dbconfig['charset']. "'");
			}
		}catch(PDOException $e){
			throw new SqlMagicException('Failed to open the DB connection', SqlMagicException::DBConnectionError);
		}
	}

	/**
	 * Reconnect to a database which has been defined in the database connection configurations
	 * @param string $db_config_name Name/key of the database configuration
	 */
	public function reconnect($db_config_name=Null){
		//$host='localhost', $db='', $user='', $password='', $driver='mysql', $persist=true
		if(isset($db_config_name)){
			$this->dbconfig = $dbconfig = $this->dbconfig_list[$db_config_name];
		}else{
			$dbconfig = $this->dbconfig;
		}
		try{
			if ($dbconfig[4]=='sqlite')
			$this->pdo = new PDO("{$dbconfig[4]}:{$dbconfig[0]}");
			else
			$this->pdo = new PDO("{$dbconfig[4]}:host={$dbconfig[0]};dbname={$dbconfig[1]}", $dbconfig[2], $dbconfig[3],array(PDO::ATTR_PERSISTENT => $dbconfig[5]));
			$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->connected = true;
			if(isset($dbconfig['charset']) && isset($dbconfig['collate'])){
				$this->pdo->exec("SET NAMES '". $dbconfig['charset']. "' COLLATE '". $dbconfig['collate'] ."'");
			}
			else if(isset($dbconfig['charset']) ){
				$this->pdo->exec("SET NAMES '". $dbconfig['charset']. "'");
			}
		}catch(PDOException $e){
			throw new SqlMagicException('Failed to open the DB connection', SqlMagicException::DBConnectionError);
		}
	}

	/**
	 * Close a database connection
	 */
	public function disconnect(){
		$this->pdo = null;
		$this->connected = false;
	}

	/**
	 * Initiates a transaction. Transactions can be nestable.
	 */
	public function beginTransaction() {
		if($this->transactionLevel === 0){
			$this->pdo->beginTransaction();
		}
		else{
			$this->pdo->exec("SAVEPOINT LEVEL{$this->transactionLevel}");
		}
		$this->transactionLevel++;
	}

	/**
	 * Commits a transaction. Transactions can be nestable.
	 */
	public function commit() {
		$this->transactionLevel--;
		if($this->transactionLevel === 0){
			$this->pdo->commit();
		}
		else{
			$this->pdo->exec("RELEASE SAVEPOINT LEVEL{$this->transactionLevel}");
		}
	}

	/**
	 * Rolls back a transaction. Transactions can be nestable.
	 */
	public function rollBack() {
		$this->transactionLevel--;
		if($this->transactionLevel === 0){
			$this->pdo->rollBack();
		}
		else{
			$this->pdo->exec("ROLLBACK TO SAVEPOINT LEVEL{$this->transactionLevel}");
		}
	}


	/**
	 * Execute a query to the connected database
	 * @param string $query SQL query prepared statement
	 * @param array $param Values used in the prepared SQL
	 * @return PDOStatement
	 */
	public function query($query, $param=null){
		if($this->sql_tracking===true){
			$querytrack = $query;
			//if params used in sql, replace them into the sql string for logging
			if($param!=null){
				if(isset($param[0])){
					$querytrack = explode('?',$querytrack);
					$q = $querytrack[0];
					foreach($querytrack as $k=>$v){
						if($k===0)continue;
						if (isset($param[$k-1])) {
							$q .=  "'".$param[$k-1]."'" . $querytrack[$k];
						} else {
							throw new Exception('Insufficent paramters provided for query');
						}
					}
					$querytrack = $q;
				}else{
					//named param used
					foreach($param as $k=>$v)
					$querytrack = str_replace($k, "'$v'", $querytrack);
				}
			}
			$this->sql_list[] = $querytrack;
			//echo '<pre>' . print_r($querytrack, true) . '</pre>';
		}

		$stmt = $this->pdo->prepare($query);
		$stmt->setFetchMode(PDO::FETCH_ASSOC);

		if($param==null)
		$stmt->execute();
		else
		$stmt->execute($param);
		return $stmt;
	}

	/*
	 * Execute a query and Fetch single row
	 * @param string $query SQL query prepared statement
	 * @param array $param Values used in the prepared SQL
	 * @param int $fetchMode  PDO Fetch mode for the statement
	 * @param mixed $fetchModeOptionalParam1 First Optional Param for PDOStatement::setFetchMode
	 * @param mixed $fetchModeOptionalParam2 Second Optional Param for PDOStatement::setFetchMode
	 * @return Returns an array a row from the result set
	 */
	public function fetchRow($query, $param = null, $fetchMode = null, $fetchModeOptionalParam1=null, $fetchModeOptionalParam2=array()) {
		$stmt = $this->query($query, $param);
		if($fetchMode!==null) {
			switch($fetchMode) {
				case PDO::FETCH_COLUMN:
					$stmt->setFetchMode($fetchMode, ($fetchModeOptionalParam1 !== null) ? $fetchModeOptionalParam1 : 0);
					break;
				case PDO::FETCH_CLASS:
					$stmt->setFetchMode($fetchMode, $fetchModeOptionalParam1, $fetchModeOptionalParam2);
					break;
				case PDO::FETCH_INTO:
					$stmt->setFetchMode($fetchMode, $fetchModeOptionalParam1);
					break;
				default:
					$stmt->setFetchMode($fetchMode);
			}
		}
		return $stmt->fetch();
	}

	/*
	 * Execute a query and Fetch multiple rows
	 * @param string $query SQL query prepared statement
	 * @param array $param Values used in the prepared SQL
	 * @param int $fetchMode  PDO Fetch mode for the statement
	 * @param mixed $fetchModeOptionalParam1 First Optional Param for PDOStatement::setFetchMode
	 * @param mixed $fetchModeOptionalParam2 Second Optional Param for PDOStatement::setFetchMode
	 * @return Returns an array containing all of the result set rows
	 */
	public function fetchAll($query, $param = null, $fetchMode = null, $fetchModeOptionalParam1=null, $fetchModeOptionalParam2=array()) {
		$stmt = $this->query($query, $param);
		if($fetchMode!==null) {
			switch($fetchMode) {
				case PDO::FETCH_COLUMN:
					$stmt->setFetchMode($fetchMode, ($fetchModeOptionalParam1 !== null) ? $fetchModeOptionalParam1 : 0);
					break;
				case PDO::FETCH_CLASS:
					$stmt->setFetchMode($fetchMode, $fetchModeOptionalParam1, $fetchModeOptionalParam2);
					break;
				case PDO::FETCH_INTO:
					$stmt->setFetchMode($fetchMode, $fetchModeOptionalParam1);
					break;
				default:
					$stmt->setFetchMode($fetchMode);
			}
		}
		return $stmt->fetchAll();
	}

	/**
	 * Use showSQL() instead.
	 * @deprecated deprecated since version 1.3
	 * @return array
	 */
	public function show_sql(){
		return $this->showSQL();
	}

	/**
	 * Retrieve a list of executed SQL queries
	 * @return array
	 */
	public function showSQL(){
		return $this->sql_list;
	}

	/**
	 * Use getQueryCount() instead
	 * @deprecated deprecated since version 1.3
	 */
	public function get_query_count(){
		return $this->getQueryCount();
	}

	/**
	 * Get the number of queries executed
	 * @return int
	 */
	public function getQueryCount(){
		return sizeof($this->sql_list);
	}

	/**
	 * Quotes a string for use in a query.
	 *
	 * Places quotes around the input string and escapes and single quotes within the input string, using a quoting style appropriate to the underlying driver.
	 * @param string $string The string to be quoted.
	 * @param int $type Provides a data type hint for drivers that have alternate quoting styles. The default value is PDO::PARAM_STR.
	 * @return string Returns a quoted string that is theoretically safe to pass into an SQL statement. Returns FALSE if the driver does not support quoting in this way.
	 */
	public function quote($string, $type=null) {
		return $this->pdo->quote($string, $type);
	}

	public function quoteArray($array, $type=null) {
		if (!is_array($array)) {
			return $this->quote($array, $type);
		}
		$numItems = count($array);
		for($i=0; $i<$numItems; $i++) {
			if (is_array($array[$i])) {
				$array[$i] = $this->quoteArray($array[$i], $type);
			} else {
				$array[$i] = $this->quote($array[$i]);
			}
		}
		return $array;
	}

	/**
	 * Returns the last inserted record's id
	 * @return int
	 */
	public function lastInsertId() {
		return $this->pdo->lastInsertId();
	}

	/**
	 * Returns the underlying PDO object used in DooSqlMagic
	 * @return PDO
	 */
	public function getDbObject(){
		return $this->pdo;
	}

	//---------------------------------- SQL generator functions ------------------------

	/**
	 * Retrieve model by one record.
	 *
	 * @param array $opt Options for the query. Available options see @see find()
	 * @return mixed A model object or associateve array of the queried result
	 */
	public function getOne($model, $opt=null){
		if($opt!==null){
			$opt['limit'] = 1;
			return $this->find($model, $opt);
		}
		return $this->find($model, array('limit'=>1));
	}

	/**
	 * Find a record. (Prepares and execute the SELECT statements)
	 * @param mixed $model The model class name or object to be select
	 * @param array $opt Associative array of options to generate the SELECT statement. Supported: <i>where, limit, select, groupby, having, param, asc, desc, custom, asArray, filters</i>
	 * @return mixed A model object or associateve array of the queried result
	 */
	public function find($model, $opt=null){
		if(is_object($model)){
			$class_name = get_class($model);

			//auto search(add conditions to WHERE) if the model propertie(s) are/is set
			$obj = get_object_vars($model);

			$wheresql ='';
			$where_values = array();
			foreach($obj as $o=>$v){
				if(isset($v) && in_array($o, $model->_fields)){
					if( is_object($v) ){
						$firstChr = substr($v, 0, 1);
						$andOrStr = ($v->useOrStatement) ? 'OR ' : 'AND';

						if(ctype_punct($firstChr)){
							if(ctype_punct(substr($v, 1, 1))){
								$firstChr .= substr($v, 1, 1);
								$wvalue = substr($v, 2);
							}else{
								$wvalue = substr($v, 1);
							}
							if($v->skipBinding === TRUE){
								$wheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr " . $wvalue;
							}else{
								$wheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr ?";
								$where_values[] = substr($v, 1);
							}
						}else{
							if($v->skipBinding === TRUE){
								$wheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
							}else{
								if(strpos(strtoupper($v), 'LIKE')===0){
									preg_match('/^LIKE[ ]{1,}[\'\"]{1}(.+)[\'\"]{1}[ ]{1,}$/i', $v, $matches);
									$wheresql .= " {$andOrStr} {$obj['_table']}.$o LIKE ?";
									$where_values[] = $matches[1];
								}
								else if(strpos(strtoupper($v), 'IS')===0){
									$wheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
								}
								else{
									$wheresql .= " {$andOrStr} {$obj['_table']}.$o=?";
									$where_values[] = $v;
								}
							}
						}
					}else{
						$wheresql .= " AND {$obj['_table']}.$o=?";
						$where_values[] = $v;
					}
				}
			}

			if($wheresql!=''){
				if(isset($opt['where'])){
					$opt['where'] .= $wheresql;
				}else{
					$opt['where'] = substr($wheresql, 5);
				}
			}
		}else{
			//require file only when String is passed in
			$this->loadModel($model);
			$class_name = $model;
			$model = new $model;
		}
		#$sqladd = array();

		//select
		if(isset($opt['select'])){
			$sqladd['select']=$opt['select'];
		}else{
			$sqladd['select'] = "{$model->_table}.*";
		}


		//limit
		if(isset($opt['limit'])){
			$sqladd['limit'] = 'LIMIT ' . $opt['limit'];
		}else{
			$opt['limit']='';
			$sqladd['limit']='';
		}

		//conditions WHERE
		if(isset($opt['where'])){
			$sqladd['where']= 'WHERE ' . $opt['where'];
		}else{
			$sqladd['where'] ='';
		}

		if (isset($opt['filters']) && is_array($opt['filters'])) {
			$sqladd['filter'] = '';
			foreach ($opt['filters'] as $filter) {
				$fmodel = null;
				if(is_object($filter['model'])){
					$fmodel = $filter['model'];
					$fTableName = $fmodel->_table;
					$fmodel_class = get_class($fmodel);
				}else{
					$fmodel = $this->loadModel($filter['model'], true);
					$fTableName = $fmodel->_table;
					$fmodel_class = $filter['model'];
				}

				list($fmodel_rtype, $fparams ) = self::relationType($this->map, $class_name, $fmodel_class);

				if(isset($filter['joinType'])){
					$joinType = $filter['joinType'] . ' JOIN';
				}else{
					$joinType = 'JOIN';
				}

				switch($fmodel_rtype) {
					case 'has_one':
						$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fparams['foreign_key']} = {$model->_table}.{$model->_primarykey} ";
						break;
					case 'belongs_to':
						list($frtype, $ffkey ) = self::relationType($this->map, $fmodel_class, $class_name);
						$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fparams['foreign_key']} = {$model->_table}.{$ffkey['foreign_key']} ";
						break;
					case 'has_many':
						list($fmtype, $fmparams) = self::relationType($this->map, $fmodel_class, $class_name);
						if ($fmtype == 'has_many') {
							$sqladd['filter'] .= "{$joinType} {$fparams['through']} ON {$model->_table}.{$model->_primarykey} = {$fparams['through']}.{$fparams['foreign_key']}\n";
							$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fmodel->_primarykey} = {$fparams['through']}.{$fmparams['foreign_key']} ";
						} else {
							$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$model->_table}.{$fmparams['foreign_key']} = {$fTableName}.{$fparams['foreign_key']} ";
						}
						break;
					default:
						throw new Exception('Table Relationship not defined for ' . $class_name . ' to ' . $fmodel_class);
				}

				if(isset($filter['where'])){
					if($sqladd['where']==''){
						$sqladd['where'] .= ' WHERE '.$filter['where'];
					}else{
						$sqladd['where'] .= ' AND '.$filter['where'].' ';
					}
					//merge the include param with the Where params, at the end
					if(isset($filter['param'])){
						if(isset($opt['param']) && isset($where_values))
						$where_values = array_merge( $where_values, $filter['param']);
						else if(isset($opt['param']))
						$opt['param'] = array_merge( $opt['param'], $filter['param']);
						else if(isset($where_values))
						$where_values = array_merge( $where_values, $filter['param']);
						else
						$where_values = $filter['param'];
					}
				}
			}
		} else {
			$sqladd['filter'] = '';
		}

		//GROUP BY
		if (isset($opt['groupby'])) {
			$sqladd['groupby'] = 'GROUP BY ' . $opt['groupby'];
		} else {
			$sqladd['groupby'] = '';
		}

		//HAVINGs
		if (isset($opt['having'])) {
			$sqladd['having'] = 'HAVING ' . $opt['having'];
		} else {
			$sqladd['having'] = '';
		}

		//if asc is defined first then ORDER BY xxx ASC, xxx DESC
		//else Order by xxx DESC, xxx ASC
		if(isset($opt['asc']) && isset($opt['desc']) && $opt['asc']!='' && $opt['desc']!=''){
			$optkeys = array_keys($opt);
			$posDesc = array_search('desc', $optkeys);
			$posAsc = array_search('asc', $optkeys);
			if($posDesc < $posAsc)
			$sqladd['order']= 'ORDER BY '. $opt['desc'] .' DESC, '. $opt['asc'] . ' ASC';
			else
			$sqladd['order']= 'ORDER BY '. $opt['asc'] .' ASC, '. $opt['desc'] . ' DESC';
		}
		//ASC ORDER
		else if(isset($opt['asc'])){
			$sqladd['order']= 'ORDER BY ' . $opt['asc'] . ' ASC';
		}
		//DESC ORDER
		else if(isset($opt['desc'])){
			$sqladd['order']= 'ORDER BY ' . $opt['desc'] . ' DESC';
		}
		else{
			$sqladd['order'] ='';
		}

		//Custom ending
		if(isset($opt['custom'])){
			$sqladd['custom']= $opt['custom'];
		}else{
			$sqladd['custom'] ='';
		}


		$sql ="SELECT {$sqladd['select']} FROM {$model->_table} {$sqladd['filter']} {$sqladd['where']} {$sqladd['groupby']} {$sqladd['having']} {$sqladd['order']} {$sqladd['custom']} {$sqladd['limit']}";

		//conditions WHERE param
		if(isset($opt['param']) && isset($where_values))
		$rs = $this->query($sql, array_merge( $opt['param'], $where_values));
		else if(isset($opt['param']))
		$rs = $this->query($sql, $opt['param']);
		else if(isset($where_values))
		$rs = $this->query($sql, $where_values);
		else
		$rs = $this->query($sql);


		//if limit is only one/first, then just return the single result, we don't need an array
		if($opt['limit']===1){
			if(isset($opt['asArray']) && $opt['asArray']===true)
			return $rs->fetch();
			else
			return $rs->fetchObject($class_name);
		}else{

			//return as Array of objects / assoc arrays

			if(isset($opt['asArray']) && $opt['asArray']===true){
				return $rs->fetchAll();
			}else{
				return $rs->fetchAll(PDO::FETCH_CLASS, $class_name);
			}
		}
	}

	/**
	 * Find a record and its associated model. Relational search. (Prepares and execute the SELECT statements)
	 * @param mixed $model The model class name or object to be select.
	 * @param string $rmodel The related model class name.
	 * @param array $opt Associative array of options to generate the SELECT statement. Supported: <i>where, limit, select, param, groupby, having, joinType, match, asc, desc, custom, asArray, include, includeWhere, includeParam, filters</i>
	 * @return mixed A list of model object(s) or associateve array of the queried result
	 */
	public function relate($model, $rmodel, $opt=null){
		if(is_object($model)){
			$class_name = get_class($model);
			//auto search(add conditions to WHERE) if the model propertie(s) are/is set
			$obj = get_object_vars($model);

			$wheresql ='';
			$where_values = array();
			foreach($obj as $o=>$v){
				if(isset($v) && in_array($o, $model->_fields)){
					if( is_object($v) ){
						$firstChr = substr($v, 0, 1);
						$andOrStr = ($v->useOrStatement) ? 'OR ' : 'AND';

						if(ctype_punct($firstChr)){
							if(ctype_punct(substr($v, 1, 1))){
								$firstChr .= substr($v, 1, 1);
								$wvalue = substr($v, 2);
							}else{
								$wvalue = substr($v, 1);
							}
							if($v->skipBinding === TRUE){
								$wheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr " . $wvalue;
							}else{
								$wheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr ?";
								$where_values[] = substr($v, 1);
							}
						}else{
							if($v->skipBinding === TRUE){
								$wheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
							}else{
								if(strpos(strtoupper($v), 'LIKE')===0){
									preg_match('/^LIKE[ ]{1,}[\'\"]{1}(.+)[\'\"]{1}[ ]{1,}$/i', $v, $matches);
									$wheresql .= " {$andOrStr} {$obj['_table']}.$o LIKE ?";
									$where_values[] = $matches[1];
								}
								else if(strpos(strtoupper($v), 'IS')===0){
									$wheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
								}
								else{
									$wheresql .= " {$andOrStr} {$obj['_table']}.$o=?";
									$where_values[] = $v;
								}
							}
						}
					}else{
						$wheresql .= " AND {$obj['_table']}.$o=?";
						$where_values[] = $v;
					}
				}
			}

			if($wheresql!=''){
				if(isset($opt['where'])){
					$opt['where'] .= $wheresql;
				}else{
					$opt['where'] = substr($wheresql, 5);
				}
			}
		}else{
			//require file only when String is passed in
			$this->loadModel($model);
			$class_name = $model;
			$model = new $model;
		}

		//select
		if(isset($opt['select'])){
			$sqladd['select']=$opt['select'];
		}else{
			$sqladd['select'] ='*';
		}

		//joinType
		if(isset($opt['joinType'])){
			$sqladd['joinType']=$opt['joinType'] . ' JOIN';
		}else{
			$sqladd['joinType'] = self::JOIN_LEFT_OUTER . ' JOIN';
		}


		//limit, value 'first' does not add LIMIT/TOP to the SQL, but will return a single result object instead of array
		if(isset($opt['limit'])){
			if($opt['limit']!='first')
			$sqladd['limit'] = 'LIMIT ' . $opt['limit'];
			else
			$sqladd['limit'] = '';
		}else{
			$opt['limit']='';
			$sqladd['limit']='';
		}


		//conditions WHERE
		if(isset($opt['where'])){
			$sqladd['where']= 'WHERE ' . $opt['where'];
		}else{
			$sqladd['where'] ='';
		}

		//GROUP BY
		if (isset($opt['groupby'])) {
			$sqladd['groupby'] = 'GROUP BY ' . $opt['groupby'];
		} else {
			$sqladd['groupby'] = '';
		}

		//HAVINGs
		if (isset($opt['having'])) {
			$sqladd['having'] = 'HAVING ' . $opt['having'];
		} else {
			$sqladd['having'] = '';
		}

		//ASC ORDER
		if(isset($opt['asc'])){
			$sqladd['order']= 'ORDER BY ' . $opt['asc'] . ' ASC';
		}else{
			$sqladd['order'] ='';
		}

		//DESC ORDER
		if(isset($opt['desc'])){
			$sqladd['order']= 'ORDER BY ' . $opt['desc'] . ' DESC';
		}

		//Custom ending
		if(isset($opt['custom'])){
			$sqladd['custom']= $opt['custom'];
		}else{
			$sqladd['custom'] ='';
		}


		//get define relation, what type, which model
		#list($rtype,$rparams) = $model->relationType($rmodel);
		if (is_object($rmodel)) {
			$relatedmodel = $rmodel;
			$rmodel =  get_class($relatedmodel);
			$rtable = $relatedmodel->_table;

			list($rtype,$rparams) = self::relationType($this->map, $class_name, $rmodel);
			if($rtype==NULL)
			throw new SqlMagicException("Model $class_name does not relate to $rmodel", SqlMagicException::RelationNotFound);

			// As we have an object we will check to see what query params are set
			// This will be used to run a nested select
			$obj = get_object_vars($relatedmodel);
			$nestedwheresql ='';
			$nested_where_values = array();
			foreach($obj as $o=>$v){
				if(isset($v) && in_array($o, $relatedmodel->_fields)){

					if( is_object($v) ){
						$firstChr = substr($v, 0, 1);
						$andOrStr = ($v->useOrStatement) ? 'OR ' : 'AND';

						if(ctype_punct($firstChr)){
							if(ctype_punct(substr($v, 1, 1))){
								$firstChr .= substr($v, 1, 1);
								$wvalue = substr($v, 2);
							}else{
								$wvalue = substr($v, 1);
							}
							if($v->skipBinding === TRUE){
								$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr " . $wvalue;
							}else{
								$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr ?";
								$nestedwheresql[] = substr($v, 1);
							}
						}else{
							if($v->skipBinding === TRUE){
								$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
							}else{
								if(strpos(strtoupper($v), 'LIKE')===0){
									preg_match('/^LIKE[ ]{1,}[\'\"]{1}(.+)[\'\"]{1}[ ]{1,}$/i', $v, $matches);
									$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o LIKE ?";
									$nested_where_values[] = $matches[1];
								}
								else if(strpos(strtoupper($v), 'IS')===0){
									$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
								}
								else{
									$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o=?";
									$nested_where_values[] = $v;
								}
							}
						}
					}else{
						$nestedwheresql .= " AND {$obj['_table']}.$o=?";
						$nested_where_values[] = $v;
					}
				}
			}

			if($nestedwheresql!=''){
				$nestedwheresql = substr($nestedwheresql, 5);
				$sqladd['rNestedQuery'] = " (SELECT * FROM {$relatedmodel->_table} WHERE {$nestedwheresql}) AS ";
			} else {
				$sqladd['rNestedQuery'] = '';
			}

			if (isset($where_values) && isset($nested_where_values)) {
				$where_values = array_merge($nested_where_values, $where_values);
			} elseif (isset($nested_where_values)) {
				$where_values = $nested_where_values;
			}

		} else {
			list($rtype,$rparams) = self::relationType($this->map, $class_name, $rmodel);
			if($rtype==NULL)
			throw new SqlMagicException("Model $class_name does not relate to $rmodel", SqlMagicException::RelationNotFound);

			$this->loadModel($rmodel);
			$relatedmodel = new $rmodel;
			$rtable = $relatedmodel->_table;

			#echo "$class_name $rtype $rmodel( $rtable )";

			$sqladd['rNestedQuery'] = '';
		}

		//reverse relation (belongs_to), checking params such as foreign_key
		#list($mtype,$mparams) = $relatedmodel->relationType($class_name);
		list($mtype,$mparams) = self::relationType($this->map, $rmodel, $class_name);
		if($mtype==NULL)
		throw new SqlMagicException("Model $rmodel does not relate to $class_name. This is a reverse check.", SqlMagicException::RelationNotFound);

		#echo "\n$rmodel $mtype $class_name( {$model->_table} )";


		//Matched true, related reference keys cannot be NULL in relationship, add Check if not null to WHERE condition
		//No need to match if it's a m to m relationship
		if(isset($opt['match']) && !($mtype=='has_many' && $rtype=='has_many'))
		if($opt['match']==true)
		self::addWhereNotNull($sqladd['where'], "{$relatedmodel->_table}.{$rparams['foreign_key']}", "{$model->_table}.{$mparams['foreign_key']}");
		elseif($opt['match']==false)
		self::addWhereIsNull($sqladd['where'], "{$relatedmodel->_table}.{$rparams['foreign_key']}", "{$model->_table}.{$mparams['foreign_key']}");


		//need to seperate the related model vars from the caller model, and add it as a Caller model's property
		//all properties of the caller model class
		$model_vars = array_keys(self::toArray($model));
		$rmodel_vars = array_keys(self::toArray($relatedmodel));

		$retrieved_pk_key = "_{$model->_table}__{$mparams['foreign_key']}";
		$model_vars[] = $retrieved_pk_key;

		$rretrieved_pk_key = "_{$relatedmodel->_table}__{$rparams['foreign_key']}";
		$rmodel_vars[] = $rretrieved_pk_key;


		//get a list of repeated vars in the 2 models, _table _relation is NOT a model property
		$repeated_vars = array_intersect($rmodel_vars, $model_vars);
		$repeated_vars = array_diff($repeated_vars, array('_table','_primarykey','_fields'));

		//if there's repeat vars(field names) in the 2 models, SELECT the 2nd model as an Alias, _table__fieldname
		//keep a set of records of the Alias names, to be replaced back later when mapping to the model object
		#$alias_vars = array();
		#$ralias_vars = array();
		$defined_class_vars = array_merge($model->_fields, $relatedmodel->_fields);
		if(isset($opt['select']))
		$oriSel = $opt['select'];
		else
		$oriSel = '*';

		foreach($repeated_vars as $r){
			//dun add user defined class properties that are not used in database
			if(!in_array($r, $defined_class_vars))continue;
			if($r==$relatedmodel->_primarykey){
				$alias = "_{$model->_table}__$r";
				$ralias = "_{$relatedmodel->_table}__$r";
				$sqladd['select'] .= ", {$model->_table}.$r AS $alias, {$relatedmodel->_table}.$r AS $ralias";
				$alias_vars[$alias] = $r;
				$ralias_vars[$ralias] = $r;
				$model_vars[] = $alias;
				$rmodel_vars[] = $ralias;

				if(isset($opt['select'])){
					if($opt['select'][0]==='*'){
						$sqladd['select'] .= ", {$relatedmodel->_table}.$r";
						$opt['select'] .= ", {$relatedmodel->_table}.$r";
					}
					else if(preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ','.$opt['select'])){
						$sqladd['select'] .= ", {$relatedmodel->_table}.$r";
						$opt['select'] .= ", {$relatedmodel->_table}.$r";
					}
				}else{
					$sqladd['select'] .= ", {$relatedmodel->_table}.$r";
					$opt['select'] = "{$relatedmodel->_table}.$r";
				}
			}
			else {
				if(isset($opt['select']) && strpos($opt['select'], ',')!==false){
					$modelHasSelectField = strpos($opt['select'], "{$model->_table}.$r")!==false;
					$rmodelHasSelectField = strpos($opt['select'], "{$relatedmodel->_table}.$r")!==false;

					if($opt['select'][0]==='*'){
						$modelHasSelectField = $rmodelHasSelectField = true;
					}else{
						if($modelHasSelectField===false && preg_match('/,(\s+)?'.$model->_table .'\.\*/', ',' . $opt['select'])){
							$modelHasSelectField = true;
						}
						if($rmodelHasSelectField===false && preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ',' . $opt['select'])){
							$rmodelHasSelectField = true;
						}
					}
				}
				else{
					//select *
					$modelHasSelectField = $rmodelHasSelectField = true;
				}

				if($modelHasSelectField===true && $rmodelHasSelectField===true){
					//					echo "\n+{$model->_table}.$r\n";
					//					echo "+{$relatedmodel->_table}.$r\n\n";
					$alias = "_{$model->_table}__$r";
					$ralias = "_{$relatedmodel->_table}__$r";
					$sqladd['select'] .= ", {$model->_table}.$r AS $alias, {$relatedmodel->_table}.$r AS $ralias";
					$alias_vars[$alias] = $r;
					$ralias_vars[$ralias] = $r;
					$model_vars[] = $alias;
					$rmodel_vars[] = $ralias;
				}
				else if($modelHasSelectField===true){
					//					echo "+{$model->_table}.$r\n\n";
					$alias = "_{$model->_table}__$r";
					$sqladd['select'] .= ", {$model->_table}.$r AS $alias";
					$alias_vars[$alias] = $r;
					$model_vars[] = $alias;
					$ralias_vars[$r] = false;
				}
				else if($rmodelHasSelectField===true){
					//					echo "+{$relatedmodel->_table}.$r\n\n";
					$ralias = "_{$relatedmodel->_table}__$r";
					$sqladd['select'] .= ", {$relatedmodel->_table}.$r AS $ralias";
					$ralias_vars[$ralias] = $r;
					$rmodel_vars[] = $ralias;
					$alias_vars[$r] = false;
				}
			}
		}

		#print_r($model_vars);
		#print_r($rmodel_vars);
		#print_r($repeated_vars);

		//include model, reduce a lot of queries, merge 3 related models together, eg. Post, PostCategory, PostComment
		if(isset($opt['include'])){
			$tmodel = null;
			if(is_object($opt['include'])){
				$tmodel = $opt['include'];
				$sqladd['include'] = $tmodel->_table;
				$tmodel_class = get_class($tmodel);
			}else{
				$tmodel = $this->loadModel($opt['include'], true);
				$sqladd['include'] = $tmodel->_table;
				$tmodel_class = $opt['include'];
			}

			list($tmodel_rtype, $tmodel_fk ) = self::relationType($this->map, $class_name, $tmodel_class);
			$tmodel_fk = $tmodel_fk['foreign_key'];

			if ($rtype == 'has_many' && $mtype == 'has_many' && $tmodel->_table == $rparams['through']) { // Is the include for the joining table?
				$sqladd['include'] = '';
			} else {
				//echo $tmodel_rtype.'<br/>';
				//include Join Type
				if(isset($opt['includeType'])){
					$sqladd['include'] = $opt['includeType'] . ' JOIN '. $sqladd['include'];
				}else{
					$sqladd['include'] = ' JOIN ' . $sqladd['include'];
				}
				if($rtype=='belongs_to'){
					list($trtype, $tfkey ) = self::relationType($this->map, $tmodel_class, $class_name);
					//echo '<h1>TMODEL '.$tfkey['foreign_key'].'</h1>';
					$sqladd['include'] .= " ON {$tmodel->_table}.$tmodel_fk = {$model->_table}.{$tfkey['foreign_key']} ";
				}else{
					$sqladd['include'] .= " ON {$tmodel->_table}.$tmodel_fk = {$model->_table}.{$model->_primarykey} ";
				}
				#print_r($sqladd['where']);
				if(isset($opt['includeWhere'])){
					if($sqladd['where']==''){
						$sqladd['where'] .= ' WHERE '.$opt['includeWhere'];
					}else{
						$sqladd['where'] .= ' AND '.$opt['includeWhere'].' ';
					}
					//merge the include param with the Where params, at the end
					if(isset($opt['includeParam'])){
						if(isset($opt['param']) && isset($where_values))
						$where_values = array_merge( $where_values, $opt['includeParam']);
						else if(isset($opt['param']))
						$opt['param'] = array_merge( $opt['param'], $opt['includeParam']);
						else if(isset($where_values))
						$where_values = array_merge( $where_values, $opt['includeParam']);
						else
						$where_values = $opt['includeParam'];
					}
				}
			}
			//edit the select part since now 3 tables are involved, might have 3 repeating field names in all tables.
			//SELECT model.*, related_model.*, includemodel.id AS _t_includemodel_id, includemodel.title AS _t_includemodel_title, ...
			$tselect_field = '';
			foreach($tmodel->_fields as $tfname){
				$tselect_field .= $tmodel->_table.'.'.$tfname . ' AS _t_' . $tmodel->_table . '_' . $tfname . ', ';
			}
			if(strpos($sqladd['select'], '*,')===0){
				$sqladd['select'] = substr($sqladd['select'], 2);
				$sqladd['select'] = "{$model->_table}.*, {$relatedmodel->_table}.*, $tselect_field" . $sqladd['select'];
			}

		}else{
			$sqladd['include']='';
		}


		if (isset($opt['filters']) && is_array($opt['filters'])) {
			$sqladd['filter'] = '';
			foreach ($opt['filters'] as $filter) {
				$fmodel = null;
				if(is_object($filter['model'])){
					$fmodel = $filter['model'];
					$fTableName = $fmodel->_table;
					$fmodel_class = get_class($fmodel);
				}else{
					$fmodel = $this->loadModel($filter['model'], true);
					$fTableName = $fmodel->_table;
					$fmodel_class = $filter['model'];
				}


				list($fmodel_rtype, $fparams ) = self::relationType($this->map, $class_name, $fmodel_class);

				if(isset($filter['joinType'])){
					$joinType = $filter['joinType'] . ' JOIN';
				}else{
					$joinType = 'JOIN';
				}

				switch($fmodel_rtype) {
					case 'has_one':
						$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fparams['foreign_key']} = {$model->_table}.{$model->_primarykey} ";
						break;
					case 'belongs_to':
						list($frtype, $ffkey ) = self::relationType($this->map, $fmodel_class, $class_name);
						$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fparams['foreign_key']} = {$model->_table}.{$ffkey['foreign_key']} ";
						break;
					case 'has_many':
						list($fmtype, $fmparams) = self::relationType($this->map, $fmodel_class, $class_name);
						if ($fmtype == 'has_many') {
							$sqladd['filter'] .= "{$joinType} {$fparams['through']} ON {$model->_table}.{$model->_primarykey} = {$fparams['through']}.{$fparams['foreign_key']}\n";
							$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fmodel->_primarykey} = {$fparams['through']}.{$fmparams['foreign_key']} ";
						} else {
							$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$model->_table}.{$fmparams['foreign_key']} = {$fTableName}.{$fparams['foreign_key']} ";
						}
				}


				#print_r($sqladd['where']);
				if(isset($filter['where'])){
					if($sqladd['where']==''){
						$sqladd['where'] .= ' WHERE '.$filter['where'];
					}else{
						$sqladd['where'] .= ' AND '.$filter['where'].' ';
					}
					//merge the include param with the Where params, at the end
					if(isset($filter['param'])){
						if(isset($opt['param']) && isset($where_values))
						$where_values = array_merge( $where_values, $filter['param']);
						else if(isset($opt['param']))
						$opt['param'] = array_merge( $opt['param'], $filter['param']);
						else if(isset($where_values))
						$where_values = array_merge( $where_values, $filter['param']);
						else
						$where_values = $filter['param'];
					}
				}
				//edit the select part since now we do not want the filter table fields. Not needed if using include as its adjusted select already
				//SELECT model.*, related_model.*, includemodel.id AS _t_includemodel_id, includemodel.title AS _t_includemodel_title, ...
				if(strpos($sqladd['select'], '*')===0){
					$sqladd['select'] = substr($sqladd['select'], 1);
					$sqladd['select'] = "{$model->_table}.*, {$relatedmodel->_table}.* " . $sqladd['select'];
				}
			}
		} else {
			$sqladd['filter'] = '';
		}


		//generate SQL based on $rtype (has_one, has_many, belongs_to)
		switch($rtype){
			case 'has_one':
				$sql = "SELECT {$sqladd['select']},  {$relatedmodel->_table}.{$rparams['foreign_key']} AS _{$relatedmodel->_table}__{$rparams['foreign_key']}
                    FROM {$model->_table}
                    {$sqladd['include']}
                    {$sqladd['filter']}
                    {$sqladd['joinType']} {$sqladd['rNestedQuery']} {$relatedmodel->_table}
                    ON {$model->_table}.{$mparams['foreign_key']} = {$relatedmodel->_table}.{$rparams['foreign_key']}
                    {$sqladd['where']}
                    {$sqladd['groupby']}
                    {$sqladd['having']}
                    {$sqladd['order']} {$sqladd['custom']} {$sqladd['limit']}";
                    break;
			case 'belongs_to':
				//for belongs_to Object reverse relate()
				$sql = "SELECT {$sqladd['select']},  {$relatedmodel->_table}.{$rparams['foreign_key']} AS _{$relatedmodel->_table}__{$rparams['foreign_key']}
                    FROM {$model->_table}
                    {$sqladd['include']}
                    {$sqladd['filter']}
                    {$sqladd['joinType']} {$sqladd['rNestedQuery']} {$relatedmodel->_table}
                    ON {$model->_table}.{$mparams['foreign_key']} = {$relatedmodel->_table}.{$rparams['foreign_key']}
                    {$sqladd['where']}
                    {$sqladd['groupby']}
                    {$sqladd['having']}
                    {$sqladd['order']} {$sqladd['custom']} {$sqladd['limit']}";
                    break;
			case 'has_many':
				if($mtype=='has_many'){

					//automatically matched for M:M relationship
					if(!isset($opt['match']) || $opt['match']==true){
						if($sqladd['where']===''){
							$sqladd['where'] = "WHERE {$relatedmodel->_table}.{$relatedmodel->_primarykey} IS NOT NULL AND {$model->_table}.{$model->_primarykey} IS NOT NULL";
						}else{
							$sqladd['where'] .= " AND {$relatedmodel->_table}.{$relatedmodel->_primarykey} IS NOT NULL AND {$model->_table}.{$model->_primarykey} IS NOT NULL";
						}
					}
					//remove the extra vars which is only exist in the link table. A_has_B
					$model_vars = array_diff($model_vars, array("_{$model->_table}__{$mparams['foreign_key']}"));
					$rmodel_vars = array_diff($rmodel_vars, array("_{$relatedmodel->_table}__{$rparams['foreign_key']}"));

					if(isset($opt['asc'])){
						$opt['asc'] = $opt['asc'] . ' ASC, ';
					}else{
						$opt['asc'] ='';
					}

					//DESC ORDER
					if(isset($opt['desc'])){
						$opt['desc'] = $opt['desc'] . ' DESC, ';
					}else{
						$opt['desc'] = '';
					}
					//if asc is defined first then ORDER BY xxx ASC, xxx DESC
					//else Order by xxx DESC, xxx ASC
					if($opt['asc']!='' && $opt['desc']!=''){
						$optkeys = array_keys($opt);
						$posDesc = array_search('desc', $optkeys);
						$posAsc = array_search('asc', $optkeys);

						if($posDesc < $posAsc)
						$addonOrder = $opt['desc'].' '.$opt['asc'];
						else
						$addonOrder = $opt['asc'].' '.$opt['desc'];
					}else{
						$addonOrder = $opt['desc'].' '.$opt['asc'];
					}

					if($opt['limit']!=''){
						if(!empty ($addonOrder)){
							if(substr($addonOrder, strlen($addonOrder)-4)=='SC, '){
								//remove ', ' at the end for the order
								$orderLimit = 'ORDER BY '.substr($addonOrder, 0, strlen($addonOrder)-2);
								//remove related Model field names from the limit for the main Model
							}else{
								$orderLimit = 'ORDER BY '.$addonOrder;
							}
							$orderLimit = preg_replace("/[, ]*$relatedmodel->_table\.[a-z0-9_-]{1,64}/i", '', $orderLimit);
							$orderLimit = str_replace('DESC ASC', 'DESC', $orderLimit);
							$orderLimit = str_replace('ASC DESC', 'ASC', $orderLimit);
							if($orderLimit=='ORDER BY ASC' || $orderLimit=='ORDER BY DESC'){
								$orderLimit = '';
							}
							if(substr(trim($orderLimit), strlen(trim($orderLimit))-1)==',')
							$orderLimit = substr(trim($orderLimit), 0, strlen(trim($orderLimit))-1). ' ';
						}

						if(isset($opt['where']) && $opt['where']!=''){
							//remove Rmodel field names from the WHERE statement
							$whrLimit = preg_replace("/[,|AND|OR ]*$relatedmodel->_table\.[a-z0-9_-]{1,64}[^{$model->_table}\.]*/i", '', $opt['where']);
							if($whrLimit!=$opt['where']){
								if(strpos(strtoupper($whrLimit), 'AND')===0){
									$whrLimit = substr($whrLimit, 3);
								}
								else{
									$tmpAnd = strpos($whrLimit, 'D ');
									if($tmpAnd!==False){
										$whrLimit = substr($whrLimit, $tmpAnd+2);
									}
								}

								//check for relatedModel WHERE statement
								if(preg_match_all("/[,|AND|OR ]*($relatedmodel->_table\.[a-z0-9_-]{1,64}[^{$model->_table}\.]*)/i", $opt['where'], $rlimitMatch)>0){
									$rlimitMatch = $rlimitMatch[0];

									$rlimitMatch = implode(' ', $rlimitMatch);

									if( substr($rlimitMatch, strlen($rlimitMatch)-4)=='AND '){
										$rlimitMatch = substr($rlimitMatch, 0, strlen($rlimitMatch)-4);
									}
									else if($rlimitMatch[strlen($rlimitMatch)-1]=='A'){
										$rlimitMatch = substr($rlimitMatch, 0, strlen($rlimitMatch)-1);
									}

									if(isset($opt['param'])){
										$rStmtLimit = $this->query("SELECT {$relatedmodel->_table}.{$relatedmodel->_primarykey} FROM {$relatedmodel->_table} WHERE {$rlimitMatch} LIMIT 1", $opt['param']);
										$rStmtId = $rStmtLimit->fetch();
										$rStmtId = $rStmtId[$relatedmodel->_primarykey];
									}else{
										$rStmtLimit = $this->query("SELECT {$relatedmodel->_table}.{$relatedmodel->_primarykey} FROM {$relatedmodel->_table} WHERE {$rlimitMatch} LIMIT 1");
										$rStmtId = $rStmtLimit->fetch();
										$rStmtId = $rStmtId[$relatedmodel->_primarykey];
									}

									$mIdStmt = $this->query("SELECT {$mparams['through']}.{$rparams['foreign_key']} FROM {$mparams['through']} WHERE {$rparams['through']}.{$mparams['foreign_key']}=? ORDER BY {$mparams['through']}.{$rparams['foreign_key']} DESC", array($rStmtId));
									//need to combine these ids with the model limit ids
									$m_in_id = array();
									foreach($mIdStmt as $m){
										$m_in_id[] = $m[$rparams['foreign_key']];
									}
								}
							}
						}

						if($opt['limit']==1 || $opt['limit']=='first'){
							$opt['limit'] = 'first';
							$limitstr = 1;
						}else{
							$limitstr = $opt['limit'];
						}

						//conditions WHERE param for the Limit
						if(isset($opt['param']) && isset($where_values) && !empty($whrLimit)){
							$countQ = 0;
							str_replace('?', '', $whrLimit, $countQ);
							if($countQ==sizeof($where_values)){
								$varsLimit = $where_values;
							}
							else{
								$varsLimit = array_merge( $opt['param'], $where_values);
							}

							$stmtLimit = $this->query("SELECT {$model->_table}.{$model->_primarykey} FROM {$model->_table} WHERE $whrLimit $orderLimit LIMIT {$limitstr}", $varsLimit);
						}else if(isset($opt['param']) && !empty($opt['param']) && !empty($whrLimit)){
							$stmtLimit = $this->query("SELECT {$model->_table}.{$model->_primarykey} FROM {$model->_table} WHERE $whrLimit $orderLimit LIMIT {$limitstr}", $opt['param']);
						}else if(isset($where_values) && !empty($where_values) && !empty($whrLimit)){
							$stmtLimit = $this->query("SELECT {$model->_table}.{$model->_primarykey} FROM {$model->_table} WHERE $whrLimit $orderLimit LIMIT {$limitstr}", $where_values);
						}else{
							if($orderLimit=='ORDER BY  ') $orderLimit = '';
							$stmtLimit = $this->query("SELECT {$model->_table}.{$model->_primarykey} FROM {$model->_table} $orderLimit LIMIT {$limitstr}");
						}

						$limitModelStr = array();
						foreach($stmtLimit as $rlimit){
							$limitModelStr[] = $rlimit[$model->_primarykey];
						}

						//combine if exists
						if(isset($m_in_id)){
							$removeIds = array_diff($limitModelStr, $m_in_id);
							foreach($removeIds as $k=>$rid){
								unset($limitModelStr[$k]);
							}
							$limitModelStr = array_unique(array_merge($limitModelStr, $m_in_id));
							$limitLength = explode(',', $opt['limit']);
							if(isset($limitLength[1])){
								array_splice($limitModelStr, $limitLength[1]);
							}else{
								array_splice($limitModelStr, $limitLength[0]);
							}
						}
						$limitModelStr = implode(',', $limitModelStr);

						if ($limitModelStr !== ''){
							if($sqladd['where']===''){
								$sqladd['where'] = "WHERE {$model->_table}.{$model->_primarykey} IN ($limitModelStr)";
							}else{
								$sqladd['where'] .= " AND {$model->_table}.{$model->_primarykey} IN ($limitModelStr)";
							}
						}
					}


					$sql = "SELECT {$sqladd['select']}, {$mparams['through']}.{$mparams['foreign_key']}, {$mparams['through']}.{$rparams['foreign_key']}
                        ,{$rparams['through']}.{$mparams['foreign_key']} AS _{$relatedmodel->_table}__{$rparams['foreign_key']}
                        ,{$rparams['through']}.{$rparams['foreign_key']} AS _{$model->_table}__{$mparams['foreign_key']}
                        FROM {$model->_table}
                        {$sqladd['include']}
                        {$sqladd['filter']}
                        {$sqladd['joinType']} {$mparams['through']}
                        ON {$model->_table}.{$model->_primarykey} = {$mparams['through']}.{$rparams['foreign_key']}
                        {$sqladd['joinType']} {$relatedmodel->_table}
                        ON {$relatedmodel->_table}.{$relatedmodel->_primarykey} = {$rparams['through']}.{$mparams['foreign_key']}
                        {$sqladd['where']}
                        {$sqladd['groupby']}
                        {$sqladd['having']}
                        ORDER BY {$addonOrder} {$rparams['through']}.{$mparams['foreign_key']},{$rparams['through']}.{$rparams['foreign_key']} ASC
                        {$sqladd['custom']}";
				}
				else if($mtype=='belongs_to'){
					$sql = "SELECT {$sqladd['select']},
					{$model->_table}.{$mparams['foreign_key']} AS _{$model->_table}__{$mparams['foreign_key']},
					{$relatedmodel->_table}.{$rparams['foreign_key']} AS _{$relatedmodel->_table}__{$rparams['foreign_key']}
                        FROM {$model->_table}
                        {$sqladd['include']}
                        {$sqladd['filter']}
                        {$sqladd['joinType']} {$sqladd['rNestedQuery']} {$relatedmodel->_table}
                        ON {$model->_table}.{$mparams['foreign_key']} = {$relatedmodel->_table}.{$rparams['foreign_key']}
                        {$sqladd['where']}
                        {$sqladd['groupby']}
                        {$sqladd['having']}
                        {$sqladd['order']} {$sqladd['custom']} {$sqladd['limit']}";
				}
				break;
		}

		//conditions WHERE param
		if(isset($opt['param']) && isset($where_values))
		$rs = $this->query($sql, array_merge( $opt['param'], $where_values));
		else if(isset($opt['param']))
		$rs = $this->query($sql, $opt['param']);
		else if(isset($where_values))
		$rs = $this->query($sql, $where_values);
		else
		$rs = $this->query($sql);

		//if limit is only one, then just return the single result, we don't need an array
		if($opt['limit']===1){
			if(isset($opt['asArray']) && $opt['asArray']===true)
			return $rs->fetch();
			else
			return $rs->fetchObject($class_name);
		}else{

			//return as Array of objects / assoc arrays

			if(isset($opt['asArray']) && $opt['asArray']===true){
				return $rs->fetchAll();
			}else{

				#$arr = array();
				if(isset($tmodel_class) && isset($record->{$tmodel_class}) ){
					$tmodelArray=null;
				}
				switch($rtype){
					case 'has_one':
					case 'belongs_to':
						foreach($rs as $k=>$v){
							if(isset($v[$retrieved_pk_key]))
							$fk = $v[$retrieved_pk_key];

							//related model foreign key, if is Null means no relation!
							$rfk = $v[$rretrieved_pk_key];

							$record = new $class_name;

							$record->{$rmodel} = ($rfk!=NULL)? new $rmodel : NULL;
							if(isset($tmodel_class) && !isset($record->{$tmodel_class}) ){
								if($tmodel_rtype=='has_many'){
									#echo 'Has Many 3rd';
									if(!isset($tmodelArray)){
										$tmodelArray = array();
										#echo '+++++ Create new 3rd Model Array';
									}
									$newtmodel = new $tmodel_class;
								}else{
									#echo 'Has One 3rd';
									$newtmodel = new $tmodel_class;
								}
							}

							foreach($v as $k2=>$v2){

								//check and add to 3rd included Model
								if(isset($newtmodel)){
									//found then add to 3rd Model, create the Model object if not yet created
									$tpart1 =  '_t_'.$tmodel->_table.'_';
									if( strpos($k2, $tpart1)===0 ){
										$tmodel_var = str_replace($tpart1, '', $k2);
										$newtmodel->{$tmodel_var} = $v2;
										continue;
									}
								}

								$gotoRelateSect = true;
								$inAsSelect = preg_match('/[\s]+(as|AS|aS|As)[\s]+'. $k2 .',/', $oriSel.',');

								if($inAsSelect || in_array($k2, $model_vars)){
									if( !$inAsSelect && $oriSel[0]!=='*' &&
									!preg_match("/,(\s+)?({$model->_table}\.)?$k2(\s+)?,/", ','.$oriSel.',') &&
									!preg_match('/,(\s+)?'.$model->_table .'\.\*/', ','. str_replace($model->_table .'.'. str_replace('_'.$model->_table.'__', '', $k2), $model->_table.'.*', $oriSel)) ){

										$gotoRelateSect = true;
									}else{
										$gotoRelateSect = false;
									}

									if($gotoRelateSect===false){
										if($k2===$retrieved_pk_key){
											$record->{$mparams['foreign_key']} = $v2;
										}else{
											//if it's a repeated var which is rename earlier(alias _table__fieldname), replace the original var with its value
											if(isset($alias_vars[$k2])){
												$record->{$alias_vars[$k2]} = $v2;
											}else{
												$record->{$k2} = $v2;
											}
										}
									}
								}

								if($gotoRelateSect===true){

									if( isset($ralias_vars[$k2]) ){
										$k2 = $ralias_vars[$k2];
									}

									if($oriSel[0]!=='*'){
										if(	in_array($k2, $rmodel_vars)===false &&
										!preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ','. str_replace($relatedmodel->_table .'.'. $k2, $relatedmodel->_table .'.*', $oriSel)) ){
											continue;
										} else if( !preg_match("/,(\s+)?({$relatedmodel->_table}\.)?($k2|\*)(\s+)?,/", ','.$oriSel.',') ){
											continue;
										}
										else if( isset($alias_vars[$k2]) ){
											continue;
										}
									}


									if($rfk!=NULL){
										if($k2===$rretrieved_pk_key){
											$record->{$rmodel}->{$rparams['foreign_key']} = $v2;
											#echo "<h3>{$rparams['foreign_key']}</h3>";
										}else{
											//if it's a repeated var which is rename earlier, replace it to its original var by spilting it _table__field '__'
											if(isset($ralias_vars[$k2])){
												#echo "<h1>{$ralias_vars[$k2]}</h1>";
												$record->{$rmodel}->{$ralias_vars[$k2]} = $v2;
											}else{
												#echo "<h2>$rmodel $k2</h2>";
												$record->{$rmodel}->{$k2} = $v2;
											}
										}
									}
								}
							}

							//add in the 3rd Model to the 3rdModel key if created
							if(isset($newtmodel)){
								if($tmodel_rtype=='has_many'){
									$tmodelArray[]= $newtmodel;
									#echo '----Added 3rd Model<br/>';
									$record->{$tmodel_class} = $tmodelArray;
								}else{
									$record->{$tmodel_class} = $newtmodel;
								}
							}
							$arr[] = $record;
						}
						break;

					case 'has_many':
						$mnull=-1;
						$model_pk_arr = array();

						foreach($rs as $k=>$v){

							$fk = $v[$retrieved_pk_key];
							if($v[$retrieved_pk_key]==Null){
								//for many to many usage, if unmatched
								$fk = $mnull--;
							}

							//related model foreign key, if is Null means no relation!
							$rfk = $v[$rretrieved_pk_key];

							if($rfk!=NULL)
							$assoc_model = new $rmodel;

							//if not a repeated record, add it on to the result array
							if(!in_array($fk, $model_pk_arr)){
								$model_pk_arr[]=$fk;
								$record = new $class_name;
								$record->{$rmodel} = ($rfk!=NULL)? array() : NULL;

								if(isset($tmodel_class) && !isset($record->{$tmodel_class}) ){
									if($tmodel_rtype=='has_many'){
										#echo 'Has Many 3rd';
										if(!isset($tmodelArray)){
											$tmodelArray = array();
											#echo '+++++ Create new 3rd Model Array';
										}
										$newtmodel = new $tmodel_class;
									}else{
										#echo 'Has One 3rd';
										$newtmodel = new $tmodel_class;
									}
								}

								foreach($v as $k2=>$v2){

									//check and add to 3rd included Model
									if(isset($newtmodel)){
										//found then add to 3rd Model, create the Model object if not yet created
										$tpart1 =  '_t_'.$tmodel->_table.'_';
										if( strpos($k2, $tpart1)===0 ){
											$tmodel_var = str_replace($tpart1, '', $k2);
											$newtmodel->{$tmodel_var} = $v2;
											continue;
										}
									}

									$gotoRelateSect = true;

									$inAsSelect = preg_match('/[\s]+(as|AS|aS|As)[\s]+'. $k2 .',/', $oriSel.',');

									if($inAsSelect || in_array($k2, $model_vars)){

										if( !$inAsSelect && $oriSel[0]!=='*' &&
										!preg_match("/,(\s+)?({$model->_table}\.)?$k2(\s+)?,/", ','.$oriSel.',') &&
										!preg_match('/,(\s+)?'.$model->_table .'\.\*/', ','. str_replace($model->_table .'.'. str_replace('_'.$model->_table.'__', '', $k2), $model->_table .'.*', $oriSel)) ){

											$gotoRelateSect = true;
										}else{
											$gotoRelateSect = false;
										}


										if($gotoRelateSect===false){
											if($k2===$retrieved_pk_key)
											$record->{$mparams['foreign_key']} = $v2;
											else{
												//if it's a repeated var which is rename earlier(alias _table__fieldname), replace the original var with its value
												if(isset($alias_vars[$k2])){
													$record->{$alias_vars[$k2]} = $v2;
												}else{
													$record->{$k2} = $v2;
												}
											}
										}
									}

									if($gotoRelateSect===true){

										if( isset($ralias_vars[$k2]) ){
											$k2 = $ralias_vars[$k2];
										}

										if($oriSel[0]!=='*'){
											if(	in_array($k2, $rmodel_vars)===false &&
											!preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ','. str_replace($relatedmodel->_table .'.'. $k2, $relatedmodel->_table .'.*', $oriSel)) ){
												continue;
											}
											else if(!preg_match("/,(\s+)?({$relatedmodel->_table}\.)?($k2|\*)(\s+)?,/", ','.$oriSel.',') ){
												continue;
											}
										}

										if($rfk!=NULL){
											if($k2===$rretrieved_pk_key){
												if(in_array($rparams['foreign_key'], $assoc_model->_fields)){
													$assoc_model->{$rparams['foreign_key']} = $v2;
												}
											}else{
												//if it's a repeated var which is rename earlier, replace it to its original var by spilting it _table__field '__'
												if(isset($ralias_vars[$k2])){
													$assoc_model->{$ralias_vars[$k2]} = $v2;
												}else if( in_array($k2, $assoc_model->_fields) ){
													$assoc_model->{$k2} = $v2;
												}
											}
										}
									}
								}

								//add in the 3rd Model to the 3rdModel key if created
								if(isset($newtmodel)){
									if($tmodel_rtype=='has_many'){
										$tmodelArray[]= $newtmodel;
										#echo '----Added 3rd Model<br/>';
										$record->{$tmodel_class} = $tmodelArray;
									}else{
										$record->{$tmodel_class} = $newtmodel;
									}
								}

								//do not add to the associated object Array if, relation not found, means empty Array, no record! if not it will create an Array with an empty Model Object
								if($rfk!=NULL)
								array_push($record->{$rmodel}, $assoc_model);
								else{
									$record->{$rmodel} = array();
								}

								$arr[] = $record;
							}
							//if already exist, then modify the model record by appending the a related object to the model class properties
							else{
								$indexToChg = array_search($fk, $model_pk_arr);
								$record = $arr[ $indexToChg ];

								foreach($v as $k2=>$v2){
									//add only vars of the second(related) model
									if(in_array($k2, $rmodel_vars)){

										if( isset($ralias_vars[$k2]) ){
											$k2 = $ralias_vars[$k2];
										}

										if($oriSel[0]!=='*'){
											if(	in_array($k2, $rmodel_vars)===false &&
											!preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ','. str_replace($relatedmodel->_table .'.'. $k2, $relatedmodel->_table .'.*', $oriSel)) ){
												continue;
											}
											else if( !preg_match("/,(\s+)?({$relatedmodel->_table}\.)?($k2|\*)(\s+)?,/", ','.$oriSel.',') ){
												continue;
											}
										}

										if($rfk!=NULL){
											if($k2===$rretrieved_pk_key)
											$assoc_model->{$rparams['foreign_key']} = $v2;
											else{
												//if it's a repeated var which is rename earlier, replace it to its original var by spilting it _table__field '__'
												if(isset($ralias_vars[$k2])){
													//														echo "<h3>$k2 = $v2</h3>";
													$assoc_model->{$ralias_vars[$k2]} = $v2;
												}else{
													//														echo "<h3>$k2 = $v2</h3>";
													$assoc_model->{$k2} = $v2;
												}
											}
										}
									}
								}

								array_push($record->{$rmodel}, $assoc_model);
								$arr[ $indexToChg ] = $record;
							}
						}

						break;
				}

				//return the first object instead of an array of objects
				if(isset($opt['limit']) && $opt['limit']=='first'){
					if(isset($newtmodel)){
						$last = sizeof($arr)-1;
						return (isset($arr[$last]))?$arr[$last] : NULL;
					}else{
						return (isset($arr[0]))?$arr[0] : NULL;
					}
				}

				return (isset($arr))?$arr : NULL;
			}
		}

	}


	/**
	 * Combine relational search results (combine multiple relates).
	 *
	 * Example:
	 * <code>
	 * Doo::db()->relateMany('Food',array('Recipe','Article','FoodType'))
	 * </code>
	 *
	 * @param mixed $model The model class name or object to be select.
	 * @param array $rmodel The related models class names.
	 * @param array $opt Array of options for each related model to generate the SELECT statement. Supported: <i>where, limit, select, param, joinType, match, asc, desc, custom, asArray, include, includeWhere, includeParam</i>
	 * @return mixed A list of model objects of the queried result
	 */
	public function relateMany($model, $rmodel, $opt=null){
		//---------------------Model has many other rmodels (has_many, has_one & belongs_to relationship only) ----------------
		//add main model primary key to select list to link the related model results

		$modelObj = $model;
		if (is_string($modelObj)) {
			$this->loadModel($modelObj);
			$modelObj = new $modelObj();
		}
		$mdl_pk = $modelObj->_primarykey;
		$mdl_tbl = $modelObj->_table;

		$rOpt = null;
		if( isset($opt[$rmodel[0]]) ){
			$rOpt = $opt[$rmodel[0]];
			if(isset($rOpt['select'])){
				$rOpt['select'] = "$mdl_tbl.$mdl_pk, " . $rOpt['select'];
			}
		}
		$mainR = $this->relate($model, $rmodel[0], $rOpt);

		if($mainR===null)
		return;

		$r=array();

		foreach($rmodel as $rm){
			if($rm==$rmodel[0])
			continue;

			$rOpt = (isset($opt[$rm])) ? $opt[$rm] : null;
			if(isset($rOpt['select'])){
				$rOpt['select'] = "$mdl_tbl.$mdl_pk, " . $rOpt['select'];
			}else{
				$this->loadModel($rm);
				$newrm = new $rm;
				$rOpt['select'] = "$mdl_tbl.$mdl_pk, {$newrm->_table}.*";
			}
			$r[] = $this->relate($model, $rm, $rOpt);
		}

		$relatedClass = $rmodel;

		foreach($mainR as $k1=>$v1){
			foreach($r as $k2=>$v2){
				$cls = $relatedClass[$k2+1];
				foreach((array)$v2 as $k3=>$v3){
					if( $v3->{$mdl_pk} == $v1->{$mdl_pk}){
						$mainR[$k1]->{$cls} = $v3->{$cls};
						break;
					}
				}
				if (!isset($mainR[$k1]->{$cls})) {
					if( is_string($model) ) {
						$relationType = self::relationType($this->map, $model, $cls);
					} else {
						$relationType = self::relationType($this->map, get_class($model), $cls);
					}

					$relationType = $relationType[0];

					if( $relationType == 'has_one' || $relationType == 'belongs_to' ) {
						$mainR[$k1]->{$cls} = null;
					} else {
						$mainR[$k1]->{$cls} = array();
					}
				}
			}
		}

		return $mainR;
	}


	/**
	 * Expand related models (Tree Relationships).
	 *
	 * Example:
	 * <code>
	 * Doo::db()->relateExpand('Recipe', array('Food','Article'))
	 * </code>
	 *
	 * @param mixed $model The model class name or object to be select.
	 * @param array $rmodel The related models class names.
	 * @param array $opt Array of options for each related model to generate the SELECT statement. Supported: <i>where, limit, select, param, joinType, match, asc, desc, custom, asArray, include, includeWhere, includeParam</i>
	 * @return mixed A list of model objects of the queried result
	 */
	public function relateExpand($model, $rmodel, $opt=null){
		$rm = $rmodel[0];
		$mainR = $this->relate($model, $rm, (isset($opt[$rm])) ? $opt[$rm] : null );
		$id = array();

		if($mainR===NULL) return;

		foreach($mainR as $mr){
			if($m = $mr->{$rm}){
				if(is_array($m)){
					foreach($m as $m2){
						$id[] = $m2->{$m2->_primarykey};
					}
				}else{
					$id[] = $m->{$m->_primarykey};
				}
			}
		}

		$this->loadModel($rm);
		$newrm = new $rm;

		$rm2 = $rmodel[1];
		$this->loadModel($rm2);
		$newrm2 = new $rm2;

		$rOpt = (isset($opt[$rm2])) ? $opt[$rm2] : null;
		if(isset($rOpt['select'])){
			$rOpt['select'] = $newrm->_table .".". $newrm->_primarykey .", " . $rOpt['select'];
		}else{
			$rOpt['select'] = $newrm->_table .".". $newrm->_primarykey .", {$newrm2->_table}.*";
		}

		if(isset($rOpt['where'])){
			$rOpt['where'] = $newrm->_table.'.'. $newrm->_primarykey .' IN ('. implode(',', $id) .') AND ( ' . $rOpt['where'] .' )';
		}else{
			$rOpt['where'] = $newrm->_table.'.'. $newrm->_primarykey .' IN ('. implode(',', $id) .') ';
		}

		$r = $this->relate($rm, $rm2, $rOpt);

		if (!empty($r)) {
			foreach($mainR as $k=>$v){
				foreach($r as $k2=>$v2){
					$rml = $v->{$rm};
					if( is_array($rml) ){
						foreach($rml as $k3=>$v3){
							if($v3->{$v3->_primarykey} == $v2->{$v2->_primarykey}){
								$mainR[$k]->{$rm}[$k3]->{$rm2} = $v2->{$rm2};
							}
						}
					}else{
						if($v->{$rm}->{$v2->_primarykey} == $v2->{$v2->_primarykey}){
							$mainR[$k]->{$rm}->{$rm2} = $v2->{$rm2};
						}
					}
				}
			}
		}

		return $mainR;
	}


	/**
	 * Adds a new record. (Prepares and execute the INSERT statements)
	 * @param object $model The model object to be insert.
	 * @return int The inserted record's Id
	 */
	public function insert($model){
		$class_name = get_class($model);

		//add values to fields where the model propertie(s) are/is set
		$obj = get_object_vars($model);

		$values = array();
		$valuestr = '';
		$fieldstr = '';

		foreach($obj as $o=>$v){
			if(isset($v) && in_array($o, $model->_fields)){
				if(is_object($v)){
					$valuestr .= "$v,";
				}else{
					$values[] = $v;
					$valuestr .= '?,';
				}
				if($this->dbconfig[4]=='mysql'){
					$fieldstr .= '`'.$o .'`,';
				}else{
					$fieldstr .= $o .',';
				}
			}
		}

		$valuestr = substr($valuestr, 0, strlen($valuestr)-1);
		$fieldstr = substr($fieldstr, 0, strlen($fieldstr)-1);

		$sql ="INSERT INTO {$obj['_table']} ($fieldstr) VALUES ($valuestr)";
		$this->query($sql, $values);
		return $this->pdo->lastInsertId();
	}

	/**
	 * Use insertAttributes() instead
	 * @deprecated deprecated since version 1.3
	 * @param string|object $model The model object to be insert.
	 * @param array Array of data (keys and values) to be insert
	 * @return int The inserted record's Id
	 */
	public function insert_attributes($model, $data){
		return $this->insertAttributes($model, $data);
	}

	/**
	 * Adds a new record with a list of keys & values (assoc array) (Prepares and execute the INSERT statements)
	 * @param string|object $model The model object to be insert.
	 * @param array Array of data (keys and values) to be insert
	 * @return int The inserted record's Id
	 */
	public function insertAttributes($model, $data){
		if(is_string($model)){
			$model = $this->loadModel($model,true);
			$table = $model->_table;
		}else{
			$table = $model->_table;
		}

		$values = array();
		$valuestr = '';
		$fieldstr = '';

		foreach($data as $o=>$v){
			if(isset($v) && in_array($o, $model->_fields)){
				if(is_object($v)){
					$valuestr .= "$v,";
				}else{
					$values[] = $v;
					$valuestr .= '?,';
				}
				if($this->dbconfig[4]=='mysql'){
					$fieldstr .= '`'.$o .'`,';
				}else{
					$fieldstr .= $o .',';
				}
			}
		}

		$valuestr = substr($valuestr, 0, strlen($valuestr)-1);
		$fieldstr = substr($fieldstr, 0, strlen($fieldstr)-1);

		$sql ="INSERT INTO {$model->_table} ($fieldstr) VALUES ($valuestr)";
		$this->query($sql, $values);
		return $this->pdo->lastInsertId();
	}


	//all rmodel belong to the first model, only 1 level relationship supported,
	//all type except BELONGS_TO (no use in INSERT)
	//if many-to-many, insert into 3 tables, the model's table, related model's table and the 'through' table defined in db.config
	//many to many need to check if the related model already exist, if so,use back that r model record's ID, add the model record and add both id to the 'through' table
	//if related model not exist, add to all 3 tables

	/**
	 * Adds a new record with its associated models. Relational insert. (Prepares and execute the INSERT statements)
	 * @param object $model The model object to be insert.
	 * @param array $rmodels A list of associated model objects to be insert along with the main model.
	 * @return int The inserted record's Id
	 */
	public function relatedInsert($model, $rmodels){

		//insert the main model first and save its id
		$main_id = $this->insert($model);
		$model->{$model->_primarykey} = $main_id;       //for later use in many-to-many relationship, need the primary key

		//loop and get their relationship, set the foreign key to $main_id
		foreach($rmodels as $rmodel){
			$class_name = get_class($model);
			$rclass_name = get_class($rmodel);
			//get how the related model relates to the main model object
			list($rtype,$rparams) = self::relationType($this->map, $rclass_name, $class_name);
			if($rtype==NULL)
			throw new SqlMagicException("Model $class_name does not relate to $rclass_name", SqlMagicException::RelationNotFound);
			#print_r(array($rtype,$rparams));

			if($rtype=='has_many' && isset($rparams['foreign_key']) && isset($rparams['through'])){
				//echo '<h2>Insert MAny to many</h2>';
				//select only the primary key (id) and the set properties
				$obj = get_object_vars($rmodel);
				$fieldstr ='';
				foreach($obj as $o=>$v){
					if(isset($v) && in_array($o, $model->_fields)){
						$fieldstr .= ','.$o;
					}
				}
				$fieldstr = "{$rmodel->_primarykey}$fieldstr";

				//get the linked key(Model's foreign key) for the $model from the relationship defined. It's not always primary key of the Model
				$reversed_relation = self::relationType($this->map, $class_name, $rclass_name);
				$model_linked_key = $reversed_relation[1]['foreign_key'];
				//$mId = $model->{$rparams['foreign_key']};
				$mId = $model->{$model->_primarykey};

				//check if the related model already exist it true than insert to the 'through' table with the 2 ids.
				$chk_rmodel = $this->find($rmodel, array('select'=>$fieldstr, 'limit'=>1));
				if($chk_rmodel!=NULL){
					//echo '<h1>'.$chk_rmodel->{$rparams['foreign_key']}.'</h1>';
					$rId = $chk_rmodel->{$chk_rmodel->_primarykey};
				}else{
					//echo '<h2>Not found this related model in many-to-many. Insert it!</h2>';
					$rId = $this->insert($rmodel);
				}

				//insert into the 'through' Table, faster with parameterized prepared statements
				$this->query( "INSERT INTO {$rparams['through']} ({$model_linked_key},{$rparams['foreign_key']})  VALUES (?,?)", array($mId,$rId));
			}
			else if(isset($rparams['foreign_key'])){
				list($rtype,$rparams) = self::relationType($this->map, $class_name, $rclass_name);
				$rmodel->{$rparams['foreign_key']} = $main_id;
				$this->insert($rmodel);
			}
		}

		return $main_id;
	}

	/**
	 * Update an existing record. (Prepares and execute the UPDATE statements)
	 * If you want to set null values during the update use the setnulls option
	 * @param mixed $model The model object to be updated.
	 * @param array $opt Associative array of options to generate the UPDATE statement. Supported: <i>where, limit, field, param, setnulls</i
	 * @return int Number of rows affected
	 */
	public function update($model, $opt=NULL){
		//add values to fields where the model propertie(s) are/is set
		$obj = get_object_vars($model);

		$values = array();
		$field_and_value = '';

		$opt['setnulls'] = isset($opt['setnulls']) ? $opt['setnulls'] : false;

		if(isset($opt['field'])){
			$opt['field'] = explode(',', str_replace(' ', '', $opt['field']));
			foreach($obj as $o=>$v) {
				if(in_array($o, $opt['field'])) {
					if (($opt['setnulls'] === true || isset($v)) && in_array($o, $model->_fields)) {
						if (is_object($v)){
							$field_and_value .= "$o=$v,";
						} elseif ($v === null) {
							$field_and_value .= "$o=null,";
						} else {
							$values[] = $v;
							$field_and_value .= $o .'=?,';
						}
					}
				}
			}
		}else{
			foreach($obj as $o=>$v){
				if(($opt['setnulls'] === true || isset($v)) && in_array($o, $model->_fields)){
					if (is_object($v)) {
						$field_and_value .= "$o=$v,";
					} elseif ($v === null) {
						$field_and_value .= "$o=null,";
					} else {
						$values[] = $v;
						$field_and_value .= $o .'=?,';
					}
				}
			}
		}

		$field_and_value = substr($field_and_value, 0, strlen($field_and_value)-1);

		if(isset($model->{$obj['_primarykey']})){
			$where = $obj['_primarykey'] .'=?';
			$values[] = $model->{$obj['_primarykey']};
			$sql ="UPDATE {$obj['_table']} SET {$field_and_value} WHERE {$where}";
		}else{
			$where = $opt['where'];
			if(isset($opt['param']))
			$values = array_merge($values, $opt['param']);

			if(isset($opt['limit'])){
				$sql ="UPDATE {$obj['_table']} SET {$field_and_value} WHERE {$where} LIMIT {$opt['limit']}";
			}else{
				$sql ="UPDATE {$obj['_table']} SET {$field_and_value} WHERE {$where}";
			}
		}

		return $this->query($sql, $values)->rowCount();
	}

	/**
	 * Update an existing record with a list of keys & values (assoc array). (Prepares and execute the UPDATE statements)
	 * @param mixed $model The model object to be updated.
	 * @param array $opt Associative array of options to generate the UPDATE statement. Supported: <i>where, limit, field, param</i>
	 * @return int Number of rows affected
	 */
	public function update_attributes($model, $data, $opt=NULL){
		if(is_string($model)){
			$model = $this->loadModel($model,true);
			$table = $model->_table;
		}else{
			$table = $model->_table;
		}

		$values = array();
		$field_and_value = '';

		if(isset($opt['field'])){
			$opt['field'] = explode(',', str_replace(' ', '', $opt['field']));
			foreach($data as $o=>$v){
				if(in_array($o, $opt['field'])){
					if(isset($v) && in_array($o, $model->_fields)){
						if(is_object($v)){
							$field_and_value .= "$o=$v,";
						}else{
							$values[] = $v;
							$field_and_value .= $o .'=?,';
						}
					}
				}
			}
		}else{
			foreach($data as $o=>$v){
				if(isset($v) && in_array($o, $model->_fields)){
					if(is_object($v)){
						$field_and_value .= "$o=$v,";
					}else{
						$values[] = $v;
						$field_and_value .= $o .'=?,';
					}
				}
			}
		}

		$field_and_value = substr($field_and_value, 0, strlen($field_and_value)-1);

		if(isset($data[$model->_primarykey])){
			$where = $model->_primarykey .'=?';
			$values[] = $data[$model->_primarykey];
			$sql ="UPDATE {$model->_table} SET {$field_and_value} WHERE {$where}";
		}else{
			$where = $opt['where'];
			if(isset($opt['param']))
			$values = array_merge($values, $opt['param']);

			if(isset($opt['limit'])){
				$sql ="UPDATE {$model->_table} SET {$field_and_value} WHERE {$where} LIMIT {$opt['limit']}";
			}else{
				$sql ="UPDATE {$model->_table} SET {$field_and_value} WHERE {$where}";
			}
		}
		return $this->query($sql, $values)->rowCount();
	}

	/**
	 * Update an existing record with its associated models. Relational update. (Prepares and execute the UPDATE statements)
	 * @param mixed $model The model object to be updated.
	 * @param array $rmodels A list of associated model objects to be updated or insert along with the main model.
	 * @param array $opt Assoc array of options to update the main model. Supported: <i>where, limit, field, param</i>
	 */
	public function relatedUpdate($model, $rmodels, $opt=NULL){
		$this->update($model, $opt);
		foreach($rmodels as $rmodel){
			#echo $rmodel->{$rmodel->_primarykey} . '<br>';
			//if related model id is set, means update the existing record!
			if(isset($rmodel->{$rmodel->_primarykey})){
				$this->update($rmodel);
			}
			//insert the related model with the relation's foreign key
			else{
				$class_name = get_class($model);
				$rclass_name = get_class($rmodel);

				//get how the related model relates to the main model object
				list($rtype,$rparams) = self::relationType($this->map, $rclass_name, $class_name);
				if($rtype==NULL)
				throw new SqlMagicException("Model $class_name does not relate to $rclass_name", SqlMagicException::RelationNotFound);
				#print_r(array($rtype,$rparams));

				if($rtype=='has_many' && isset($rparams['foreign_key']) && isset($rparams['through'])){
					//echo '<h2>Insert MAny to many</h2>';
					//select only the primary key (id) and the set properties
					$obj = get_object_vars($rmodel);
					$fieldstr ='';
					foreach($obj as $o=>$v){
						if(isset($v) && in_array($o, $model->_fields)){
							$fieldstr .= ','.$o;
						}
					}
					$fieldstr = "{$rmodel->_primarykey}$fieldstr";

					//get the linked key(Model's foreign key) for the $model from the relationship defined. It's not always primary key of the Model
					$reversed_relation = self::relationType($this->map, $class_name, $rclass_name);
					$model_linked_key = $reversed_relation[1]['foreign_key'];
					//$mId = $model->{$rparams['foreign_key']};
					$mId = $model->{$model->_primarykey};

					//check if the related model already exist it true than insert to the 'through' table with the 2 ids.
					$chk_rmodel = $this->find($rmodel, array('select'=>$fieldstr, 'limit'=>1));
					if($chk_rmodel!=NULL){
						//echo '<h1>'.$chk_rmodel->{$rparams['foreign_key']}.'</h1>';
						$rId = $chk_rmodel->{$chk_rmodel->_primarykey};
					}else{
						//echo '<h2>Not found this related model in many-to-many. Insert it!</h2>';
						$rId = $this->insert($rmodel);
					}

					//insert into the 'through' Table, if exists dun create duplicates
					if($this->query("SELECT {$rparams['foreign_key']},{$model_linked_key} FROM {$rparams['through']} WHERE {$rparams['foreign_key']}=? AND {$model_linked_key}=?", array($rId,$mId))->fetch()==NULL){
						$this->query( "INSERT INTO {$rparams['through']} ({$model_linked_key},{$rparams['foreign_key']}) VALUES (?,?)", array($mId,$rId));
					}
				}
				else if(isset($rparams['foreign_key'])){
					//echo '<h2>Insert 1to1 or 1tomany</h2>';
					list($rtype,$rparams) = self::relationType($this->map, $class_name, $rclass_name);
					$rmodel->{$rparams['foreign_key']} = $model->{$model->_primarykey};
					$this->insert($rmodel);
				}
			}
		}
	}

	/**
	 * Delete all records from table (Prepares and executes the DELETE statement)
	 * @param mixed $model The model object from which to delete all records
	 */
	public function deleteAll($model) {
		if (!is_object($model)) {
			$this->loadModel($model);
			$model = new $model;
		}

		$sql = "DELETE FROM {$model->_table}";
		$rs = $this->query($sql);
	}

	/**
	 * Delete an existing record. (Prepares and execute the DELETE statements)
	 * @param mixed $model The model object to be deleted.
	 * @param array $opt Associative array of options to generate the UPDATE statement. Supported: <i>where, limit, param</i>
	 */
	public function delete($model, $opt=NULL){
		if(is_object($model)){
			//add values to fields where the model propertie(s) are/is set
			$obj = get_object_vars($model);

			//WHERE statement needs to change to parameterized stament =? instead of =value
			$wheresql ='';
			$where_values = array();

			foreach($obj as $o=>$v){
				if(isset($v) && in_array($o, $model->_fields)){
					$wheresql .= " AND {$obj['_table']}.$o=?";
					$where_values[] = $v;
				}
			}

			if($wheresql!=''){
				if(isset($opt['where'])){
					$opt['where'] .= $wheresql;
				}else{
					$opt['where'] = substr($wheresql, 5);
				}
			}
		}else{
			//require file only when String is passed in
			$this->loadModel($model);
			$model = new $model;
		}

		if(isset($opt['limit']))
		$sql ="DELETE FROM {$model->_table} WHERE {$opt['where']} LIMIT {$opt['limit']}";
		else
		$sql ="DELETE FROM {$model->_table} WHERE {$opt['where']}";

		//conditions WHERE param
		if(isset($opt['param']) && isset($where_values))
		$rs = $this->query($sql, array_merge( $opt['param'], $where_values));
		else if(isset($opt['param']))
		$rs = $this->query($sql, $opt['param']);
		else if(isset($where_values))
		$rs = $this->query($sql, $where_values);
		else
		$rs = $this->query($sql);
	}

	/**
	 * Retrieve the relationship between 2 model class
	 * @param array $map The relationship mapping
	 * @param string $model_name Main model name
	 * @param string $relate_model_name Related model name
	 * @return array Relationship with details such as relationship type, foreign key, linked table
	 */
	public static function relationType($map, $model_name, $relate_model_name){
		if (!isset($map[$model_name])) {
			throw new SqlMagicException("No relationship mapping found between '{$model_name}' and '{$relate_model_name}'");
		}
		$r1 = $map[$model_name];
		#print_r($r1);
		$rtype = NULL;

		foreach($r1 as $k=>$v){
			#echo "$k<ul>";
			foreach($v as $m=>$n){
				#echo "<li>$m</li>";
				if($m == $relate_model_name){
					$rtype = $k;
					break 2;
				}
			}
			#echo "</ul>";
		}

		//return relation type, relation params (foreign_key, through)
		return array($rtype, $n);
	}

	protected static function addWhereNotNull(&$where, $relatedKey, $modelKey){
		if(empty($where)){
			$where = "WHERE ($relatedKey IS NOT NULL AND $modelKey IS NOT NULL)";
		}else{
			$where .= " AND ($relatedKey IS NOT NULL AND $modelKey IS NOT NULL)";
		}
	}

	protected static function addWhereIsNull(&$where, $relatedKey, $modelKey){
		if(empty($where)){
			$where = "WHERE ($relatedKey IS NULL OR $modelKey IS NULL)";
		}else{
			$where .= " AND ($relatedKey IS NULL OR $modelKey IS NULL)";
		}
	}

	public static function toObject($array, $class_name) {
		$object = new $class_name;
		if (is_array($array) && count($array) > 0) {
			foreach ($array as $name=>$value) {
				$name = strtolower(trim($name));
				if (!empty($name)) {
					$object->$name = $value;
				}
			}
		}
		return $object;
	}

	public static function toArray($object) {
		if (is_object($object)) {
			$array = get_object_vars($object);
		}
		return $array;
	}

}

class SqlMagicException extends Exception {
	const UnexpectedClass   = 0;
	const RelationNotFound = 1;
	const DBConfigNotFound = 2;
	const DBConnectionError = 3;
}
